<?php

namespace Henan\ThinkSdk\utils;

use Exception;
use PHPExcel;
use PHPExcel_IOFactory;

/**
 * EXCEL表格工具
 */
class ExcelUtil
{
    /**
     * 读取
     * @param string $filename excel文件
     * @param int $skip 跳过行数
     * @return array
     * @throws Exception
     */
    public static function read(string $filename, int $skip = 0): array
    {
        try {
            $data = [];
            $inputFileName = $filename;
            $inputFileType = PHPExcel_IOFactory::identify($inputFileName);
            $objReader = PHPExcel_IOFactory::createReader($inputFileType);
            $objPHPExcel = $objReader->load($inputFileName);
            $worksheet = $objPHPExcel->getActiveSheet();
            $highestRow = $worksheet->getHighestRow();
            $highestColumn = $worksheet->getHighestColumn();
            for ($row = 1; $row <= $highestRow; $row++) {
                if ($row <= ($skip + 1)) continue;
                $item = [];
                for ($column = 'A'; $column <= $highestColumn; $column++) {
                    $cell = $worksheet->getCell($column . $row);
                    $item[] = $cell->getValue();
                }
                $data[] = $item;
            }
            $objPHPExcel->disconnectWorksheets();
            unset($objPHPExcel);
            return $data;
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }

    /**
     * 输出
     * @param array $cols
     * @param array $data
     * @param string $title
     * @param $outPath
     * @return void
     * @throws Exception
     */
    public static function output(array $cols = [], array $data = [], string $title = '未命名', $outPath = null): void
    {
        try {
            $letters = ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V', 'W', 'X', 'Y', 'Z'];
            $keys = array_keys($cols);
            $ths = array_values($cols);
            $objPHPExcel = new PHPExcel();
            $objPHPExcel->getProperties()->setCreator("Your Name")
                ->setLastModifiedBy("Your Name")
                ->setTitle("Test Document")
                ->setSubject("Test Document")
                ->setDescription("Test document for PHPExcel, generated using PHP classes.")
                ->setKeywords("office PHPExcel php")
                ->setCategory("Test result file");
            $obj = $objPHPExcel->setActiveSheetIndex(0);
            foreach ($ths as $index => $th) {
                $obj->setCellValue($letters[$index] . '1', $th);
            }
            foreach ($data as $n => $item) {
                $colNum = $n + 2;
                $obj = $objPHPExcel->setActiveSheetIndex(0);
                foreach ($keys as $index => $name) {
                    $obj->setCellValue($letters[$index] . $colNum, $item[$name] ?? '');
                }
            }
            $objPHPExcel->getActiveSheet()->setTitle($title);
            // 导出Excel文件
            $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
            if ($outPath) {
                $objWriter->save($outPath);
            } else {
                header('Content-Type: application/vnd.ms-excel');
                header('Content-Disposition: attachment;filename="' . $title . '.xlsx"');
                header('Cache-Control: max-age=0');
                $objWriter->save('php://output');
                exit;
            }
        } catch (Exception $e) {
            throw new Exception($e->getMessage());
        }
    }
}